# real implementation should drop stuff like 
# "".format, 
# abstracting away sharding strategies
# due to inefficiences of function calls and object init, parameter passing ...

from __future__ import generators    # needs to be at the top of your module

from bottle import route, post, get, request
from jinja2 import Environment, FileSystemLoader

from result_controller import db_conn_manager
from result_controller import result_sharding_strategy
from result_controller import card_sharding_strategy

import mysql.connector

class MySQLCursorDict(mysql.connector.cursor.MySQLCursor):

  def fetchone(self):
    row = self._fetch_row()

    if row:
      return dict(zip(self.column_names, self._row_to_python(row)))
    return None

class data_controller:

  @get("/data/load/cards/<number_of_records>")
  def load_cards(number_of_records):

    shard1 = card_sharding_strategy.get_shard(1)
    shard2 = card_sharding_strategy.get_shard(4)
    shard3 = card_sharding_strategy.get_shard(7)

    conn_mgr = db_conn_manager()
    connection_shard1 = conn_mgr.connect(shard1['host'], shard1['user'], shard1['passwd'], shard1['db'])
    connection_shard2 = conn_mgr.connect(shard2['host'], shard2['user'], shard2['passwd'], shard2['db'])
    connection_shard3 = conn_mgr.connect(shard3['host'], shard3['user'], shard3['passwd'], shard3['db'])

    # prepare a cursor object using cursor() method
    cursor_shard1 = connection_shard1.cursor (cursor_class=MySQLCursorDict)
    cursor_shard2 = connection_shard2.cursor (cursor_class=MySQLCursorDict)
    cursor_shard3 = connection_shard3.cursor (cursor_class=MySQLCursorDict)

    # execute the SQL query using execute() method.
    for i in range(0, int(number_of_records)):
      sql = ("INSERT INTO card(id, pin, numberOfUsages) VALUES (0, '{0}', 0) ".format(i))

      str_card_pin_last_digit = str(i)[-1:]

      if str_card_pin_last_digit in ["1","2","3"]:
        cursor_shard1.execute (sql)
      elif str_card_pin_last_digit in ["4","5","6"]:
        cursor_shard2.execute (sql)
      elif str_card_pin_last_digit in ["7","8","9","0"]:
        cursor_shard3.execute (sql)

    # close the cursor object
    cursor_shard1.close ()
    cursor_shard2.close ()
    cursor_shard3.close ()

    connection_shard1.close()
    connection_shard2.close()
    connection_shard3.close()

    return '<div class="alert alert-success">Complete. <a href="/">Return</a></div>'

  @get("/data/create_dbs")
  def create_dbs():
    import sys

    f = open(r'create_sql.sql')
    sqls = f.readlines()

    import mysql.connector
    connection = mysql.connector.connect(passwd='', user='root', host='localhost')
    cursor = connection.cursor (cursor_class=MySQLCursorDict)

    for i in range(0, len(sqls)):
        if sqls[i].strip() == "":
            continue

        cursor.execute(sqls[i])
        connection.commit()
        
    cursor.close()
    connection.close()

    return '<div class="alert alert-success">Complete. <a href="/">Return</a></div>'


  @get("/data/reset/mysql_memory")
  def reset_mysql_memory():
    conn_mgr = db_conn_manager()
    connection = conn_mgr.connect('localhost', 'root', '', '_jamb_result_memory')

    # prepare a cursor object using cursor() method
    cursor = connection.cursor (cursor_class=MySQLCursorDict)
    
    cursor.execute ("DELETE FROM result")
    
    # close the cursor object
    cursor.close ()
    
    connection.close()

    return '<div class="alert alert-success">Complete. <a href="/">Return</a></div>'

  
  @get("/data/reset/mysql_shards")
  def reset_mysql_shards():
    
    # laziness
    shard1 = result_sharding_strategy.get_shard(1)
    shard2 = result_sharding_strategy.get_shard(4)
    shard3 = result_sharding_strategy.get_shard(7)    
    
    conn_mgr = db_conn_manager()
    connection_shard1 = conn_mgr.connect(shard1['host'], shard1['user'], shard1['passwd'], shard1['db'])
    connection_shard2 = conn_mgr.connect(shard2['host'], shard2['user'], shard2['passwd'], shard2['db'])
    connection_shard3 = conn_mgr.connect(shard3['host'], shard3['user'], shard3['passwd'], shard3['db'])

    # prepare a cursor object using cursor() method
    cursor_shard1 = connection_shard1.cursor (cursor_class=MySQLCursorDict)
    cursor_shard2 = connection_shard2.cursor (cursor_class=MySQLCursorDict)
    cursor_shard3 = connection_shard3.cursor (cursor_class=MySQLCursorDict)
    
    cursor_shard1.execute ("DELETE FROM result")
    cursor_shard2.execute ("DELETE FROM result")
    cursor_shard3.execute ("DELETE FROM result")
    
    # close the cursor object
    cursor_shard1.close ()
    cursor_shard2.close ()
    cursor_shard3.close ()
    
    connection_shard1.close()
    connection_shard2.close()
    connection_shard3.close()

    return '<div class="alert alert-success">Complete. <a href="/">Return</a></div>'

    
  @get("/data/reset/memcache")
  def reset_memcached():
    import memcache as memcache   
    
    mc = memcache.Client(servers=['127.0.0.1:11211'], debug=1)
    
    # might shard too
    mc.flush_all()

    return '<div class="alert alert-success">Complete. <a href="/">Return</a></div>'
  
  @get("/data/load/mysql_shards/<number_of_records>")
  def load_mysql_shards(number_of_records):
  
    records = randomizer.get_result_records(int(number_of_records))
    
    # laziness
    shard1 = result_sharding_strategy.get_shard(1)
    shard2 = result_sharding_strategy.get_shard(4)
    shard3 = result_sharding_strategy.get_shard(7)    
    
    conn_mgr = db_conn_manager()
    connection_shard1 = conn_mgr.connect(shard1['host'], shard1['user'], shard1['passwd'], shard1['db'])
    connection_shard2 = conn_mgr.connect(shard2['host'], shard2['user'], shard2['passwd'], shard2['db'])
    connection_shard3 = conn_mgr.connect(shard3['host'], shard3['user'], shard3['passwd'], shard3['db'])

    # prepare a cursor object using cursor() method
    cursor_shard1 = connection_shard1.cursor (cursor_class=MySQLCursorDict)
    cursor_shard2 = connection_shard2.cursor (cursor_class=MySQLCursorDict)
    cursor_shard3 = connection_shard3.cursor (cursor_class=MySQLCursorDict)
    
    # execute the SQL query using execute() method.    
    for i in range(0, int(number_of_records)):
      # copy of record to perform justus upon
      record = records[i]
      
      sql_parts = data_controller.get_formated_statement(record)
      
      sql = "INSERT INTO result({0}) VALUES({1})".format(sql_parts[0], sql_parts[1])
      
      examination_number = record['examination_number']
      str_exam_number_last_digit = str(examination_number)[-1:]
      
      if str_exam_number_last_digit in ["1","2","3"]:
        cursor_shard1.execute (sql)
      elif str_exam_number_last_digit in ["4","5","6"]:
        cursor_shard2.execute (sql)
      elif str_exam_number_last_digit in ["7","8","9","0"]:
        cursor_shard3.execute (sql)
    
    # close the cursor object
    cursor_shard1.close ()
    cursor_shard2.close ()
    cursor_shard3.close ()
    
    connection_shard1.close()
    connection_shard2.close()
    connection_shard3.close()
      
    return '<div class="alert alert-success">Complete. <a href="/">Return</a></div>'
    
  # load now happens from data in shards
  @get("/data/load/mysql_memory")
  def load_mysql_memory():
    conn_mgr = db_conn_manager()

    keys = [
        'examination_number',
        'candidate_name',
        'gender',
        'state_of_origin',
        'local_govt',
        'result1',
        'result2',
        'result3',
        'result4',
        'aggregate',
        'choice1_ms_institution',
        'choice1_mr_institution',
        'choice1_p_institution',
        'choice2_ms_institution',
        'choice2_mr_institution',
        'choice2_p_institution',
        'status',
    ]

    keys_string = (','.join(keys))
    
    connection = conn_mgr.connect('localhost', 'root', '', '_jamb_result_memory')

    # prepare a cursor object using cursor() method
    cursor = connection.cursor (cursor_class=MySQLCursorDict)

    # cursor.execute ("SET session max_heap_table_size = 734003200;")
    # cursor.execute ("SET GLOBAL max_heap_table_size = 734003200;")

    for i in ["1", "2", "3"]:
        sql = "insert into _jamb_result_memory.result ({0}) select {0} from _jamb_result_shard_{1}.result;".format(keys_string, i)
        cursor.execute (sql)
    
    # close the cursor object
    cursor.close ()
    
    connection.close()

    return '<div class="alert alert-success">Complete. <a href="/">Return</a></div>'
  
  @get("/data/load/memcache")
  def load_memcache():
    import memcache as memcache   
    
    mc = memcache.Client(servers=['127.0.0.1:11211'], debug=1)
    _numbers = ["11", "44", "77"]
    conn_mgr = db_conn_manager()
    for i in ["1","2","3"]:
      
      # print ("shard" + i)
      
      # laziness
      shard = result_sharding_strategy.get_shard(_numbers[int(i) - 1])
      # print(shard)
      connection = conn_mgr.connect(shard['host'], shard['user'], shard['passwd'], shard['db'])
      
      # prepare a cursor object using cursor() method
      cursor = connection.cursor (cursor_class=MySQLCursorDict)
      
      # execute the SQL query using execute() method.
      cursor.execute ("SELECT * FROM result")      

      for row in data_controller.ResultIter(cursor):
        mc.set(row['examination_number'], row)
              
      # close the cursor object
      cursor.close ()
      
      connection.close()

    return '<div class="alert alert-success">Complete. <a href="/">Return</a></div>'
  
  def get_formated_statement(record): 
    
    # save the aggregate value
    aggregate = record['aggregate']
    status = record['status']
    
    # remove the aggregate field
    del record['aggregate']
    del record['status']
    if 'id' in record.keys():
        del record['id']
    
    keys_string = (','.join(record.keys())) + ", " + "`aggregate`, id, `status`"
    values_string = "'" + (','.join(record.values())).replace(',', "','") + "', " + str(aggregate) + ", 0, true"
    
    keys_string.replace('status', '`status`')
    
    return (keys_string, values_string)
    
  # This code require Python 2.2.1 or later
  def ResultIter(cursor, arraysize=1000):
      'An iterator that uses fetchmany to keep memory usage down'
      while True:
          results = cursor.fetchmany(arraysize)
          if not results:
              break
          for result in results:
              yield result
    
from random import *
    
class randomizer: 
  
  def get_card_records(number):
    return []
    
  def get_result_records(number):    
  
    examination_numbers = range(10100000, 19900000)
    
    results = [None] * (number)
    first_names = randomizer.generate_strings(number, 5, 10) 
    last_names = randomizer.generate_strings(number, 5, 10) 
    
    results1 = randomizer.generate_strings(number, 7, 18)
    results2 = randomizer.generate_strings(number, 7, 18)
    results3 = randomizer.generate_strings(number, 7, 18)
    results4 = randomizer.generate_strings(number, 7, 18)
    
    lgs = { 'Kano':['Rimin Gado'] }
    
    # generate a few universities
    _universities = randomizer.generate_strings(30, 8, 17) 
    universities = list(map(lambda uni: uni + ' ' + randomizer.generate_strings(1, 4, 10)[0]  + ' University', _universities))
    
    # generate a few polytechniques
    _polytechniques = randomizer.generate_strings(30, 8, 17) 
    polytechniques = list(map(lambda poly: poly + ' ' + randomizer.generate_strings(1, 4, 10)[0]  + ' Polytechnique', _polytechniques))
    
    # generate a few colleges of education
    _coes = randomizer.generate_strings(30, 8, 17) 
    coes = list(map(lambda coe: coe + ' ' + randomizer.generate_strings(1, 4, 10)[0]  + ' College of Education', _coes))
    
    for i in range(0, number):
      
      gender = choice(['Male', 'Female'])
      sof = choice(["Kano"])
      lg = choice(lgs[sof])
      
      grades = [ randrange(0, 101), randrange(0, 101), randrange(0, 101), randrange(0, 101) ]
      
      result1 = results1[i] + ' = ' + str(grades[0])
      result2 = results2[i] + ' = ' + str(grades[1])
      result3 = results3[i] + ' = ' + str(grades[2])
      result4 = results4[i] + ' = ' + str(grades[3])
      
      # laziness at its peak
      import operator
      aggregate = sum(grades)
      
      results[i] = { 
        'examination_number': str(examination_numbers[i]),
        'candidate_name': (first_names[i] + ' ' + last_names[i]).title(),
        'gender': gender,
        'state_of_origin': sof,
        'local_govt': lg,
        'result1': result1.title(),
        'result2': result2.title(),
        'result3': result3.title(),
        'result4': result4.title(),
        'aggregate': aggregate,       
        
        # don't bother coding for the delusion that a candidate will apply for a polytechnique
        # or college of education before applying university. THIS IS NIGERIA!.
        'choice1_ms_institution': choice(universities),
        'choice1_mr_institution': choice(polytechniques),
        'choice1_p_institution': choice(coes),
        
        'choice2_ms_institution': choice(universities),
        'choice2_mr_institution': choice(polytechniques),
        'choice2_p_institution': choice(coes),
        
        'status': 1
      }
  
    return results
    
  def generate_strings(length, min, max):
    
    string = [None] * length 
    for i in range(0, length):
      r = randrange(min, max + 1)
      chr_list = [None] * r
      for i2 in range(0, r):
        chr_list[i2] = chr(randrange(65, 90))
      string[i] = (''.join(chr_list)).title()
          
    return string